<?php

namespace MLM\Model\Member;


use MLM\Model\Member;

class Storage extends \MLM\Model\Generic\Db\Storage {
    
    public function save(Member $member){
        $insert = $this->_sql->insert()
                ->into('members')
                ->values($member->pull());
        
        $result = $this->execute($insert);
        
        return $result;
    }
    
    /*
     * Fungsi untuk load parent yang mungkin dari memberId
     * select dari member yang berada pada jaringan hirarki.
     */
    public function listParent($memberId){
        
        $stmt = $this->_db->query("
		select member_id, member_code, member_name, getCountChild(?) cnt from members where member_id = ? 
		UNION
			SELECT  hi.member_id,hi.member_code, hi.member_name, getCountChild(hi.member_id) cnt
                FROM (
                    SELECT  CPriorParent(member_id) AS member_id, @level AS level
                    FROM (
                        SELECT  @start_with := ?,@id := @start_with,@level := 0
                    ) vars, members
                    WHERE   @id IS NOT NULL
                ) ho
                JOIN    members hi
                ON      hi.member_id = ho.member_id");
        return $stmt->execute(array($memberId, $memberId, $memberId)) ;
    }
    
    public function addRequest($memberId,$type){
        $insert = $this->_sql->insert()
                ->into('service_request')
                ->values(array(
                    'member_id'=> $memberId,
                    'request_type' => $type,
                    'request_dtm' => $this->now()
                    ));
        
        return $this->execute($insert);
    }


    public function getSN($code){
        $select = $this->_sql->select()
                ->from('sn')
                ->where(array('sn_code'=> $code));
        
        return $this->fetchRow($select);
    }
	
	public function updateSn($sn,$status){
		$sql = $this->update()
					->table('sn')
					->set(array('sn_status' => $status))
					->where(array('sn_code' => $sn));
					
		$rst = $this->execute($sql);
				
		return $rst;
	}

        public function getChild($parentId){
		$sql = "select getCountChild(?) as cnt";
		$stmt = $this->_db->query($sql);
        $res = $stmt->execute(array($parentId));
		
		return $res;
	}
	
    public function getMemberDetail($key){

        /*
        $sql = "select y.*, up.member_code as upline_code, up.member_name as upline_name from (
                select x.*, sp.member_code as sponsor_code, sp.member_name as sponsor_name from (
                    select m.member_id, m.member_code, m.member_name, m.pin, a.contact_name, a.contact_email, a.contact_mobile,
                    a.contact_phone, a.contact_address, a.contact_city, a.contact_province, a.country_id, a.post_code,
                    account_name, bank_acc_no, bank_city_name, bank_acc_name, member_type_name,
                    m.sponsor_id, m.parent_id,
                    m.matrik1_id, DATE_FORMAT(m.start_dtm, '%d-%m-%Y') as start_dtm
                    from members m, member_contact c, contact a, accounts ac, member_type mt
                    where m.member_id = ?
                    and c.member_id = m.member_id
                    and a.contact_id = c.contact_id
                    and ac.member_id = m.member_id and mt.member_type_id = m.member_type_id
                    ) x left outer join members sp on sp.member_id = x.sponsor_id
                    ) y left outer join members up on up.member_id = y.parent_id";
         *
         */



        $sql = "select y.*, up.member_code as upline_code, up.member_name as upline_name from (
                select x.*, sp.member_code as sponsor_code, sp.member_name as sponsor_name from (
                    select m.*, mt.member_type_name, c.*
                    from members m, member_type mt, member_contact mc, contact c
                    where m.member_id = ? and mt.member_type_id = m.member_type_id
                    and mc.member_id = m.member_id and c.contact_id = mc.contact_id
                    ) x left outer join members sp on sp.member_id = x.sponsor_id
                    ) y left outer join members up on up.member_id = y.parent_id ";


        $stmt = $this->_db->query($sql);
        $res = $stmt->execute(array($key));


        return $res;
    }



    public function getMemberDetailByCode($key){


        $sql = "select y.*, up.member_code as upline_code, up.member_name as upline_name from (
                select x.*, sp.member_code as sponsor_code, sp.member_name as sponsor_name from (
                    select m.*, mt.member_type_name
                    from members m, member_type mt
                    where m.member_code = ? and mt.member_type_id = m.member_type_id
                    ) x left outer join members sp on sp.member_id = x.sponsor_id
                    ) y left outer join members up on up.member_id = y.parent_id ";


        $stmt = $this->_db->query($sql);
        $res = $stmt->execute(array($key));

        return $res;
    }

    
    public function getNewMember($memberId, $num = 1){

        $sql = "select * from members where sponsor_id = ? order by member_id desc limit $num";
        $stmt = $this->_db->query($sql);
        return $stmt->execute(array($memberId)) ;

    }

    public function getNumMembers($memberId){

        /*$sql = "select FORMAT(count(1),0) as total
                    from members where member_type_id = 2 ";
         * 
         */

        $sql = "select FORMAT(count(1),0) as total
            from members m, member_contact mc
            where member_type_id = 2 and mc.member_id = m.member_id ";

        if ($memberId != ""){
            $sql .=  " and m.matrik1_id = $memberId ";
        }

        $stmt = $this->_db->query($sql);
        return $stmt->execute();

    }

    public function getNumMitra($memberId){

        $sql = "SELECT  count(1) as total
              FROM (
              SELECT  CPriorMatrik(member_id) AS member_id, @level AS level
              FROM (
              SELECT  @start_with := ?, @id := @start_with
              ) vars, members
              WHERE   @id IS NOT NULL and member_type_id = 2
              ) x ";

        $stmt = $this->_db->query($sql);
        return $stmt->execute(array($memberId)) ;

    }

    
    public function getNumTransaction($accNum){

        $sql = "select count(1) as total, FORMAT(sum(amount1),0) as jumlah
                from v_transaction where account_num1 = ? ";

        $stmt = $this->_db->query($sql);
        return $stmt->execute(array($accNum)) ;

    }

    public function updateMember($data, $where){

        $sql = $this->update()
                    ->table('members')
                    ->set($data)
                    ->where($where);

        $result = $this->execute($sql);

        return $result;
    }

    
    public function getPerkembanganMember($date1, $date2, $start, $limit, $searchField = "") {

        if ($limit != ""){
            $sql = "select count(1) as total from( ";


            $sql .= " select date_format(start_dtm,'%Y%m%d') period, 4 as jenis
                    from members
                    where member_type_id = 2 and STR_TO_DATE(DATE_FORMAT(start_dtm, '%Y%m%d'),'%Y%m%d') between STR_TO_DATE('$date1', '%Y%m%d') and STR_TO_DATE('$date2', '%Y%m%d') ";


            $sql .= " group by period, jenis) a"; // and a.id_reff1=th.user_ref";

    
            $stmt = $this->_db->query($sql);
            $getTotal = $stmt->execute();

            $rows = 0;
            while($getTotal->next()){
                    $res = $getTotal->current();
                    $rows = $res['total'];
            }

            $data = array($rows); 
        }



        $sql = "select period, FORMAT(total_bonus,0) as total_bonus,
                FORMAT(sisa_bonus,0) as sisa_bonus
                from ( ";

        $sql .= "select date_format(start_dtm,'%Y-%m-%d') as period, count(member_id) as total_bonus, max(0) as sisa_bonus
                from members
                where member_type_id = 2 and STR_TO_DATE(DATE_FORMAT(start_dtm, '%Y%m%d'),'%Y%m%d') between STR_TO_DATE('$date1', '%Y%m%d') and STR_TO_DATE('$date2', '%Y%m%d')
                group by date_format(start_dtm,'%d-%m-%Y')";


        $sql .=  ") xx where total_bonus > 0 order by period desc ";

        if ($limit != ""){
            $sql .=  " limit $start, $limit ";
        }


        $stmt = $this->_db->query($sql);
        $result = $stmt->execute();

        $datanya = array();
        while($result->next()){
                $res = $result->current();

                $jumlah = "<a href='../member/memberlist?regdate=".$res['period']."'>".$res['total_bonus']."</a>";

                array_push($datanya, array("Tanggal" => $res['period'], "Jumlah" => $jumlah));
        }

        array_push($data, $datanya);

        return $data;
    }


    public function addAgent($memberId, $accNum, $memberName, $address){

        $dataInput = array($memberId, $accNum, $memberName, $address);

        $stmt = $this->_db->query("CALL Trx_AddAgent( ?, ?, ?, ?, @out_code, @out_msg)", $dataInput);
        $stmt = $this->_db->query("SELECT @out_code as out_code, @out_msg as out_msg");
        $data = $stmt->execute();

        return $data;

    }


    public function getAgentList($agentCode, $agentName, $addr, $start, $limit, $searchField = "") {

        $sql = "select count(1) as total
                from members m, member_contact mc, contact c
                where member_reff_id is not null and mc.member_id = m.member_id
                and c.contact_id = mc.contact_id ";

        if ($agentCode != ""){
            $sql .=  " and member_code like '%$agentCode%' ";
        }

        if ($agentName != ""){
            $sql .=  " and member_name like '%$agentName%' ";
        }

        if ($addr != ""){
            $sql .=  " and (contact_address like '%$addr%' or contact_city like '%$addr%' or contact_province like '%$addr%') ";
        }

        if ($searchField != ""){
            $sql .= " and $searchField ";
        }

        $stmt = $this->_db->query($sql);
        $getTotal = $stmt->execute();

        while($getTotal->next()){
                $res = $getTotal->current();
                $rows = $res['total'];                
        }


        $data = array($rows);


        $sql = "select m.*, c.*
                from members m, member_contact mc, contact c
                where member_reff_id is not null and mc.member_id = m.member_id
                and c.contact_id = mc.contact_id ";

        if ($agentCode != ""){
            $sql .=  " and member_code like '%$agentCode%' ";
        }

        if ($agentName != ""){
            $sql .=  " and member_name like '%$agentName%' ";
        }

        if ($addr != ""){
            $sql .=  " and (contact_address like '%$addr%' or contact_city like '%$addr%' or contact_province like '%$addr%') ";
        }

        if ($searchField != ""){
            $sql .= " and $searchField ";
        }

        $sql .= " order by member_code ";

        if ($limit != ""){
            $sql .= " limit $start, $limit";
        }

        $stmt = $this->_db->query($sql);
        $result = $stmt->execute();

        $dataBonus = array();
        while($result->next()){
                $res = $result->current();
                $alamat = $res['contact_address']." ".$res['contact_city']." ".$res['contact_province'];
                
                array_push($dataBonus, array("KodeAgen" => $res['member_code'],
                                             "NamaAgen" => $res['member_name'],
                                             "Alamat" => $alamat));
        }


        array_push($data, $dataBonus);

        return $data;
    }


    public function getMemberList($code, $name, $addr, $start, $limit, $searchField = "", $regdate = "") {

        $sql = "select count(1) as total
                from members m, member_contact mc, contact c
                where member_type_id = 2 and mc.member_id = m.member_id
                and c.contact_id = mc.contact_id ";

        if ($code != ""){
            $sql .=  " and member_code like '%$code%' ";
        }

        if ($name != ""){
            $sql .=  " and member_name like '%$name%' ";
        }

        if ($addr != ""){
            $sql .=  " and (contact_address like '%$addr%' or contact_city like '%$addr%' or contact_province like '%$addr%') ";
        }

        if ($regdate != ""){
            $sql .=  " and STR_TO_DATE(DATE_FORMAT(start_dtm, '%Y%m%d'),'%Y%m%d') = STR_TO_DATE('$regdate', '%Y%m%d') ";
        }

        if ($searchField != ""){
            $sql .= " and $searchField ";
        }

        $stmt = $this->_db->query($sql);
        $getTotal = $stmt->execute();

        while($getTotal->next()){
                $res = $getTotal->current();
                $rows = $res['total'];
        }


        $data = array($rows);


        $sql = "select m.*, c.* 
                from members m, member_contact mc, contact c
                where member_type_id = 2 and mc.member_id = m.member_id
                and c.contact_id = mc.contact_id ";

        if ($code != ""){
            $sql .=  " and member_code like '%$code%' ";
        }

        if ($name != ""){
            $sql .=  " and member_name like '%$name%' ";
        }

        if ($addr != ""){
            $sql .=  " and (contact_address like '%$addr%' or contact_city like '%$addr%' or contact_province like '%$addr%') ";
        }

        if ($regdate != ""){
            $sql .=  " and STR_TO_DATE(DATE_FORMAT(start_dtm, '%Y%m%d'),'%Y%m%d') = STR_TO_DATE('$regdate', '%Y%m%d') ";
        }

        if ($searchField != ""){
            $sql .= " and $searchField ";
        }

        $sql .= " order by member_code ";

        if ($limit != ""){
            $sql .= " limit $start, $limit";
        }

        $stmt = $this->_db->query($sql);
        $result = $stmt->execute();

        $dataBonus = array();
        while($result->next()){
                $res = $result->current();
                $alamat = $res['contact_address']." ".$res['contact_city']." ".$res['contact_province'];

                $aksiPop = '<a data-toggle="modal" class="blue" role="button" href="#detail-form" onclick="detail(\''.$res['member_id'].'\')">Detail Akun</a>';
                
                array_push($dataBonus, array("Kode" => $res['member_code'],
                                             "Nama" => $res['member_name'],
                                             "Hp" => $res['contact_mobile'],
                                             "Alamat" => $alamat,
                                             "Detail" => $aksiPop));
        }


        array_push($data, $dataBonus);

        return $data;
    }


    public function getMemberMatrix($memberId, $lvl, $code, $name, $start, $limit, $searchField = "") {

        $sql = "SELECT  count(1) as total
                FROM (
                    SELECT  CPriorParent(member_id) AS member_id, @level AS level
                    FROM (
                        SELECT  @start_with := ?,@id := @start_with,@level := 0
                    ) vars, members
                    WHERE   @id IS NOT NULL
                ) ho,
                members hi
                where hi.member_id = ho.member_id ";

        if ($code != ""){
            $sql .=  " and hi.member_code like '%$code%' ";
        }

        if ($name != ""){
            $sql .=  " and hi.member_name like '%$name%' ";
        }


        if ($searchField != ""){
            $sql .= " and $searchField ";
        }

        

        $stmt = $this->_db->query($sql);
        $getTotal = $stmt->execute(array($memberId));

        while($getTotal->next()){
                $res = $getTotal->current();
                $rows = $res['total'];
        }


        $data = array($rows);


        $sql = "SELECT  level, hi.member_id,hi.member_code, hi.member_name,
                sp.member_code as sponsor_code, up.member_code as upline_code,
                IF(hi.status_id=1,'AKTIF','TIDAK AKTIF') as status
                FROM (
                    SELECT  CPriorParent(member_id) AS member_id, @level AS level
                    FROM (
                        SELECT  @start_with := ?,@id := @start_with,@level := 0
                    ) vars, members
                    WHERE   @id IS NOT NULL
                ) ho,
                members hi, members sp, members up
                where hi.member_id = ho.member_id
                and sp.member_id = hi.sponsor_id and
                up.member_id = hi.parent_id ";

        if ($code != ""){
            $sql .=  " and member_code like '%$code%' ";
        }

        if ($name != ""){
            $sql .=  " and member_name like '%$name%' ";
        }

        if ($lvl != ""){
            $sql .=  " and level = '$lvl' ";
        }

        if ($searchField != ""){
            $sql .= " and $searchField ";
        }

        $sql .= " order by level, member_code ";

        if ($limit != ""){
            $sql .= " limit $start, $limit";
        }
        
        $stmt = $this->_db->query($sql);
        $result = $stmt->execute(array($memberId));

        $dataMatrix = array();
        while($result->next()){
                $res = $result->current();

                array_push($dataMatrix, array("Level" => $res['level'],
                                             "Kode" => $res['member_code'],
                                             "Nama" => $res['member_name'],
                                             "Sponsor" => $res['sponsor_code'],
                                             "Upline" => $res['upline_code'],
                                             "Status" => $res['status']
                                         ));
        }


        array_push($data, $dataMatrix);

        return $data;
    }
}
